# -*- coding: utf-8 -*-
##this file is import and update the security from stox (reading "TickerInfo.xls")
import xlrd
import MySQLdb

# connect mysql
db = MySQLdb.connect(host="localhost", port=3306, user="root",
                     passwd="123456", db="quant_data", charset='utf8')
cursor=db.cursor()
temp = cursor.execute("""SELECT security_id, short_name FROM `security` where `security_type`=1 """)
temp = cursor.fetchall()
ids = dict((y, x) for x, y in temp)

#sys.setdefaultencoding('utf-8')
wb = xlrd.open_workbook('TickerInfo.xls')
sh = wb.sheet_by_index(0)

industryIDs = [240,280,334,305,142,281,271,242,171,203,147,279,201,148,202,241,264,170,231]
count = 0
for i in range(1, sh.nrows):
    
    #get data
    exchangeID = sh.cell(i, 3).value
    ticker = sh.cell(i, 1).value
    industryKey = sh.cell(i,5).value
    if exchangeID == 0: #hose
        exchangeID = 2
    elif exchangeID == 1:#hnx
        exchangeID = 1
    else:               #otc / others
        exchangeID = 0    
    vi_name = sh.cell(i,2).value    
    industryID = industryIDs.index(industryKey) + 1
    
    #write sql 
    if ticker in ids:
        #update
        sql = """UPDATE `security` SET `sector_id`='%d',`viet_name`='%s' where `short_name` = '%s'""" % (industryID,vi_name, ticker)        
    else: 
        #insert
        sql = """INSERT INTO `security` (`security_id`,`short_name`,`viet_name`
                ,`sector_id`,`security_type`,`trading_destination_id`) 
                VALUES (0,'%s','%s',%d,1,%d)""" %(vi_name,ticker,industryID,exchangeID)
        
        count= count +1
    
    sql = sql.encode('utf-8')
    
    temp = cursor.execute(sql)

cursor.close()
db.commit()
db.close()